import pandas as pd
import os

p_path = os.getcwd()
dir_path = p_path + "/处理结果/"

order_list = pd.read_excel(dir_path + "2.订单列表.xlsx")
settled_list = pd.read_excel(dir_path + "1.交易收入.xlsx")
# pd.set_option("display.max_rows", None, "display.max_columns", 100)

print(f'结算数据：{len(settled_list)} 条，订单数据： {len(order_list)} 条')

order_list.drop(
    "店铺,收货人姓名,收货人联系方式,详细地址1,详细地址2,详细地址3,区县,城市,省份,收货地址邮编,邮箱,国家".split(','),
    axis=1, inplace=True)

pd.set_option("display.max_rows", None, "display.max_columns", 100)

result_list = pd.merge(settled_list, order_list, how='left', left_on=['订单编号', '子订单号'],
                       right_on=['订单号', '子订单号'])
result_list.drop('订单号', axis=1, inplace=True)

# print(result_list.columns)

# hebing_list = result_list.groupby(['订单编号','SKU货号'])[['交易收入','商品件数']].sum().reset_index()
# print(hebing_list)

# result_list = pd.merge(settled_list,hebing_list,how='right',left_on=['订单编号','子订单号'],right_on=['订单号','子订单号'])

# hebing_list = result_list.groupby(['订单编号','SKU货号'])[['交易收入','商品件数','其他成本']].sum().reset_index()
#
result_list.to_excel(dir_path + "3.处理过的结算数据.xlsx", index=False)

print(f'合并完成，合并之后的数据量：{len(result_list)} 条.')
#
